package com.lovo.dao.impl;

import com.lovo.bean.TotalBean;
import com.lovo.dao.BaseDao;
import com.lovo.dao.ITotalDao;

import java.util.ArrayList;
import java.util.List;

public class TotalDaoImpl extends BaseDao implements ITotalDao {
    @Override
    public List<TotalBean> findAll() {
        List list = new ArrayList();
        String sql = "select city ,count(*) school_count ," +
                "(select count(*) from school s1 where s1.city=s.city and status='接洽中') in_contact_count," +
                "(select count(*) from school s1 where s1.city=s.city and status='待审') pending_count," +
                "(select count(*) from school s1 where s1.city=s.city and status='推广开展') passed_count," +
                "(select count(*) from school s1 where s1.city=s.city and status='审核未通过') failed_count " +
                "from school s GROUP BY city";

        try{
            setConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                TotalBean totalBean = new TotalBean();
                totalBean.setName(rs.getString("city"));
                totalBean.setSchoolCount(rs.getInt("school_count"));
                totalBean.setInContactCount(rs.getInt("in_contact_count"));
                totalBean.setPendingCount(rs.getInt("pending_count"));
                totalBean.setPassedCount(rs.getInt("passed_count"));
                totalBean.setFailedCount(rs.getInt("failed_count"));
                list.add(totalBean);
            }

        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConnection();
        }
        return list;

    }

    public static void main(String[] args) {
        TotalDaoImpl totalDao = new TotalDaoImpl();
        System.out.println(totalDao.findAll());
    }
}
